﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Collections;

/// <summary>
/// Summary description for terminateDAL
/// </summary>
public class TerminateDAL
{
	public TerminateDAL()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public void terminate(int insuredID,String termianteReason)
    {
        SqlConnection con = DBManager.getSqlConnection();
        con.Open();
        String sql = "UPDATE INSURED SET " +
                      "policyStatus=@policyStatus, " +
                      "terminateDate=@terminateDate," +
                      "terminateReason=@terminateReason" +
                      " where insuredID=@insuredID";

        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@policyStatus", "TERMINATED");
        cmd.Parameters.AddWithValue("@terminateDate", DateTime.Now);
        cmd.Parameters.AddWithValue("@terminateReason", termianteReason);
        cmd.Parameters.AddWithValue("@insuredID", insuredID);
        cmd.ExecuteNonQuery();

        /*sql = "SELECT I.startDate FROM INSURANCE A INNER JOIN INSURED I ON " +
                           "A.accountNo = I.accountNo " +
                           "WHERE I.insuredID=@insuredID";

        cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@policyStatus", "TERMINATED");
        cmd.Parameters.AddWithValue("@insuredID", insuredID);
        SqlDataReader dr = cmd.ExecuteReader();
        DateTime startDate;
        if(dr.Read()){

            startDate = dr.GetDateTime(0);

        }

        dr.Close();*/
        con.Close();

    }
    public DateTime getStartDate(int insuredID)
    {
        SqlConnection con = DBManager.getSqlConnection();
        con.Open();
       
        String sql = "SELECT I.startDate FROM INSURANCE A INNER JOIN INSURED I ON " +
                           "A.accountNo = I.accountNo " +
                           "WHERE I.insuredID=@insuredID";

        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@policyStatus", "TERMINATED");
        cmd.Parameters.AddWithValue("@insuredID", insuredID);
        SqlDataReader dr = cmd.ExecuteReader();
        DateTime startDate = DateTime.MinValue;
        if (dr.Read())
        {

            startDate = dr.GetDateTime(0);

        }

        dr.Close();
        con.Close();

        return startDate;
    }
    public Hashtable getInsuranceAccountNo(int insuredID)
    {
        SqlConnection con = DBManager.getSqlConnection();
        con.Open();
       
        String sql = "SELECT accountNo,premium FROM INSURED I " +
                           "WHERE I.insuredID=@insuredID";

        SqlCommand cmd = new SqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@insuredID", insuredID);
        SqlDataReader dr = cmd.ExecuteReader();
        Hashtable ht = new Hashtable();

        if (dr.Read())
        {
            
            ht.Add("accountNo", dr.GetString(0));
            ht.Add("premium", dr.GetDecimal(1));
           

        }

        dr.Close();
        con.Close();

        return ht;
    }
}